package com.wolfpire.subject.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.subject.dao.SubjectDao;
import com.wolfpire.system.common.Constants;
import com.wolfpire.system.common.Page;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.system.dao.UserDao;
import com.wolfpire.system.model.Profession;
import com.wolfpire.system.model.Section;
import com.wolfpire.system.model.Subject;
import com.wolfpire.system.model.User;
import com.wolfpire.system.model.UserGroup;
import com.wolfpire.system.model.UserGroupSubjectRelation;

@Repository("subjectDao")
public class SubjectDaoImpl extends BaseHibernateDao<Subject, Long> implements SubjectDao {

	public List<Subject> list(Subject subject) {
		Criteria c = createCriteria(createFilter(subject));
		return c.list();
	}

	public Page<Subject> findSubjects(Page<Subject> page, Subject filterSubject) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		//querySqlBuffer.append(" SELECT u.* FROM t_sm_user u, t_sm_org o, t_sm_user_org uo ")
		querySqlBuffer.append(" SELECT s.* FROM t_subject s ")
				.append(" LEFT JOIN t_profession_subject ps ON s.id = ps.subject_id  ")
				.append(" LEFT JOIN t_profession_subject p ON p.id = ps.profession_id ")
				.append(" WHERE 1 =1 ");
				//.append(" WHERE u.id = uo.user_id AND uo.org_id = o.id ");
		if (null != filterSubject.getId()) {
			querySqlBuffer.append(" s.id =:id");
			propertyNames.add("id");
			propertyValues.add(filterSubject.getId());
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterSubject.getName()))) {
			querySqlBuffer.append(" AND s.name LIKE :name ");
			propertyNames.add("name");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterSubject.getName()) + "%");
		}
		
		if (null != filterSubject.getProfessionId()) {
			querySqlBuffer.append(" AND ps.profession_id= :professionId ");
			propertyNames.add("professionId");
			propertyValues.add(filterSubject.getProfessionId());
		}
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY s.id DESC ");
		if (0 < totalCount) {
			List<Subject> result = this.setPageParameter(page, Subject.class, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(result);
		}
		
		return page;
	}
	
	private Criterion[] createFilter(Subject subject) {
		if (null == subject) {
			return null;
		}
		List<Criterion> criterions = new ArrayList<Criterion>();
		if (null != subject.getId()) {
			criterions.add(Restrictions.eq("id", subject.getId()));
		}
		if (!StringUtils.isBlank(subject.getName())) {
			criterions.add(Restrictions.like("name", subject.getName(), MatchMode.ANYWHERE));
		}
		return criterions.toArray(new Criterion[criterions.size()]);
	}

	public Subject getByName(String name) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("name", name)
		};
		return findUniqueByCriteria(criterions);
	}

	public List<Map<String, Object>> querySubjectFullInfo() {
		// TODO Auto-generated method stub
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT s.id, s.name, ps.profession_id FROM t_subject s LEFT JOIN t_profession_subject ps ON s.id = ps.subject_id");
		SQLQuery query = this.createSqlQuery(querySql.toString(), null, null);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
    /**
     * 查询出指定条数的数据
     */
	public List<Subject> list(int num) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select * from t_subject s ORDER BY create_time desc limit ");
		querySql.append(num);
		SQLQuery query = this.createSqlQuery(querySql.toString(), null, null);
		query.addEntity(Subject.class);
		return query.list();
	}
	/**
	 * 根据Id查询对象
	 */
	public Subject getById(long id) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("id", id)
		};
		return findUniqueByCriteria(criterions);
	}
	/**
	 * 根据用户组的ID，查询用户组下的课程
	 */
	public List<Subject> findSubjectByUserGroupId(long userGroupId) {
		StringBuffer querySql = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySql.append(" select s.* from t_subject s,t_usergroup_subject ugs  ");
		querySql.append(" where s.id = ugs.subject_id and ugs.usergroup_id = :userGroupId  ");
		propertyNames.add("userGroupId");
		propertyValues.add(userGroupId);
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames, propertyValues);
		query.addEntity(Subject.class);
		return query.list();
	}
	/**
	 * admin用户返回全部的课程
	 */
	public List<Subject> findAllSubject() {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select s.* from t_subject s");
		SQLQuery query = this.createSqlQuery(querySql.toString(), null, null);
		query.addEntity(Subject.class);
		return query.list();
	}
	/**
	 * 点击用户组，展示被关联过的课程列表
	 */
	public Page<Map<String, Object>> findSubjects(Page<Map<String, Object>> page, Subject filterSubject,
			long usergroupId) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		//querySqlBuffer.append(" SELECT u.* FROM t_sm_user u, t_sm_org o, t_sm_user_org uo ")
		querySqlBuffer.append(" SELECT s.* FROM t_subject s ")
				.append(" LEFT JOIN t_profession_subject ps ON s.id = ps.subject_id  ")
				.append(" LEFT JOIN t_profession_subject p ON p.id = ps.profession_id ")
				.append(" WHERE 1 =1 ");
				//.append(" WHERE u.id = uo.user_id AND uo.org_id = o.id ");
		if (null != filterSubject.getId()) {
			querySqlBuffer.append(" s.id =:id");
			propertyNames.add("id");
			propertyValues.add(filterSubject.getId());
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterSubject.getName()))) {
			querySqlBuffer.append(" AND s.name LIKE :name ");
			propertyNames.add("name");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterSubject.getName()) + "%");
		}
		
		if (null != filterSubject.getProfessionId()) {
			querySqlBuffer.append(" AND ps.profession_id= :professionId ");
			propertyNames.add("professionId");
			propertyValues.add(filterSubject.getProfessionId());
		}
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY s.id DESC ");
		SQLQuery query = this.createSqlQuery(querySqlBuffer.toString(), propertyNames, propertyValues);
		query.addEntity(Subject.class);
		List<Subject> subjects = query.list();
		//用户组和课程的关联表,查询出对象组
		StringBuffer querySql = new StringBuffer();
		List<String> propertyNames1 = new ArrayList<String>();
		List propertyValues1 = new ArrayList();
		querySql.append(" select ugs.* from t_usergroup_subject ugs where ugs.usergroup_id = :usergroupId");
		propertyNames1.add("usergroupId");
		propertyValues1.add(usergroupId);
		SQLQuery query1 = this.createSqlQuery(querySql.toString(), propertyNames1, propertyValues1);
		query1.addEntity(UserGroupSubjectRelation.class);
		List<UserGroupSubjectRelation> lists1 = query1.list();
		List<Long> s = new ArrayList<Long>();
		for(UserGroupSubjectRelation userGroupSubjectRelation : lists1)
			s.add(userGroupSubjectRelation.getSubjectId());                      //用户组中的课程
		
		List<Map<String, Object>> lists = new ArrayList<Map<String,Object>>();
		for(Subject subject : subjects){
			Map<String, Object> subjectMap = new HashMap<String, Object>();
			if(s.contains(subject.getId()))                                     //被勾选过的课程
				subjectMap.put("ck", true);
			else
				subjectMap.put("ck", false);                                    //未勾选的课程
			subjectMap.put("id", subject.getId());
			subjectMap.put("name", subject.getName());
			subjectMap.put("remark", subject.getRemark());
			subjectMap.put("createTime", subject.getCreateTime());
			lists.add(subjectMap);
		}
		if (0 < totalCount) {
			/*List<Map<String, Object>> result = this.setPageParameter(page, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(result);*/
			page.setDataList(lists);
		}
		/*if (0 < totalCount) {
			List<Map<String,Object>> result = this.setPageParameter(page, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(result);
		}*/
		
		return page;
	}
	
}
